package cn.mapper;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import cn.pojo.Buy;
import cn.pojo.Goods;
import cn.util.DBUtil;

public class GoodsMapperImp extends DBUtil implements GoodsMapper {

	@Override
	public int totalCount(String goodsName) {
		String sql = "select count(id) from `smbms_goods` where 1=1 ";
		List<Object> list = new ArrayList<Object>();
		if (goodsName != null && !goodsName.equals("")) {
			sql += " and goodsName like ? ;";
			list.add("%" + goodsName + "%");
		}
		Connection ct = getConnection();
		PreparedStatement ps = null;
		ResultSet st = null;
		try {
			ps = ct.prepareStatement(sql);
			for (int i = 0; i < list.size(); i++) {
				ps.setObject((i + 1), list.get(i));
			}
			st = ps.executeQuery();
			while (st.next()) {
				return st.getInt(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			release(ct, ps, st);
		}
		return 0;
	}

	@Override
	public List<Goods> getGoodsLike(String goodsName, Integer rowNum, int pagecount) {
		String sql = "select * from `smbms_goods` where 1=1 ";
		List<Object> list = new ArrayList<Object>();
		if (goodsName != null && !goodsName.equals("")) {
			sql += " and goodsName like ? ";
			list.add("%" + goodsName + "%");
		}
		sql += " limit ?,? ;";
		list.add(rowNum);
		list.add(pagecount);
		System.out.println(sql);
		Connection ct = getConnection();
		PreparedStatement ps = null;
		ResultSet st = null;
		List<Goods> list1 = new ArrayList<Goods>();
		try {
			ps = ct.prepareStatement(sql);
			for (int i = 0; i < list.size(); i++) {
				ps.setObject((i + 1), list.get(i));
			}
			st = ps.executeQuery();
			while (st.next()) {
				Goods g = new Goods();
				g.setGoodsCode(st.getString("goodsCode"));
				g.setGoodsDESC(st.getString("goodsDesc"));
				g.setGoodsName(st.getString("goodsName"));
				g.setGoodsPrice(st.getInt("goodsPrice"));
				g.setGoodsUnit(st.getString("goodsUnit"));
				g.setId(st.getInt("id"));
				list1.add(g);
			}
			return list1;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			release(ct, ps, st);
		}
		return null;
	}

	@Override
	public int toAddGoods(Buy b) {
		Connection ct = getConnection();
		PreparedStatement ps = null;
		ResultSet st = null;
		String sql = " insert into smbms_buy (billCode, productName, productDesc, productUnit, productCount, totalPrice, isPayment, createdBy, creationDate, providerId) values(?,?,?,?,?,?,?,?,?,?) ";
		try {
			ps = ct.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
			ps.setObject(1, b.getBillCode());
			ps.setObject(2, b.getProductName());
			ps.setObject(3, b.getProductDesc());
			ps.setObject(4, b.getProductUnit());
			ps.setObject(5, b.getProductCount());
			ps.setObject(6, b.getTotalPrice());
			ps.setObject(7, b.getIsPayment());
			ps.setObject(8, b.getCreatedBy());
			ps.setObject(9, b.getCreationDate());
			ps.setObject(10, b.getProviderId());
			int i = ps.executeUpdate();
			st = ps.getGeneratedKeys();
			ct.commit();
			while (st.next()) {
				return st.getInt(1);
			}
		} catch (Exception e) {
			try {
				ct.rollback();
			} catch (Exception e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		} finally {
			release(ct, ps, null);
		}
		return 0;
	}

	@Override
	public Goods getGoodsByid(String id) {
		String sql = "select * from smbms_goods where id=?;";
		List<Goods> list = new DBUtil().get(Goods.class, sql, id);
		return list.get(0);
	}

	@Override
	public List<Buy> getBuyLike(String productName, Integer rowNum, int pagecount) {
		String sql = "select * from `smbms_buy` where 1=1 ";
		List<Object> list = new ArrayList<Object>();
		if (productName != null && !productName.equals("")) {
			sql += " and productName like ? ";
			list.add("%" + productName + "%");
		}
		sql += " limit ?,? ;";
		list.add(rowNum);
		list.add(pagecount);
		System.out.println(sql);
		Connection ct = getConnection();
		PreparedStatement ps = null;
		ResultSet st = null;
		List<Buy> list1 = new ArrayList<Buy>();
		try {
			ps = ct.prepareStatement(sql);
			for (int i = 0; i < list.size(); i++) {
				ps.setObject((i + 1), list.get(i));
			}
			st = ps.executeQuery();
			while (st.next()) {
				Buy b = new Buy();
				b.setBillCode(st.getString("billCode"));
				b.setCreatedBy(st.getLong("createdBy"));
				b.setCreationDate(st.getTimestamp("creationDate"));
				b.setId(st.getLong("id"));
				b.setIsPayment(st.getInt("isPayment"));
				b.setModifyBy(st.getLong("modifyBy"));
				b.setModifyDate(st.getTimestamp("modifyDate"));
				b.setProductCount(st.getDouble("productCount"));
				b.setProductDesc(st.getString("productDesc"));
				b.setProductName(st.getString("productName"));
				b.setProductUnit(st.getString("productUnit"));
				b.setProviderId(st.getInt("providerId"));
				b.setTotalPrice(st.getDouble("totalPrice"));
				list1.add(b);
			}
			return list1;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			release(ct, ps, st);
		}
		return null;
	}

	@Override
	public int totalCountByBuy(String productName) {
		String sql = "select count(id) from `smbms_buy` where 1=1 ";
		List<Object> list = new ArrayList<Object>();
		if (productName != null && !productName.equals("")) {
			sql += " and productName like ? ;";
			list.add("%" + productName + "%");
		}
		Connection ct = getConnection();
		PreparedStatement ps = null;
		ResultSet st = null;
		try {
			ps = ct.prepareStatement(sql);
			for (int i = 0; i < list.size(); i++) {
				ps.setObject((i + 1), list.get(i));
			}
			st = ps.executeQuery();
			while (st.next()) {
				return st.getInt(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			release(ct, ps, st);
		}
		return 0;
	}

	@Override
	public List<Buy> getBuyByid(String[] id) {
		String sql = " select * from smbms_buy where id in ( ";
		List<Buy> list = new ArrayList<Buy>();
		if (id == null) {
			return list;
		}
		if (id.length == 0) {
			return list;
		}
		for (int i = 0; i < id.length; i++) {
			if (i < id.length - 1) {
				sql += id[i] + ",";
			} else {
				sql += id[i];
			}
		}
		sql += " );";
		Connection ct = getConnection();
		PreparedStatement ps = null;
		ResultSet st = null;
		try {
			ps = ct.prepareStatement(sql);
			st = ps.executeQuery();
			while (st.next()) {
				Buy b = new Buy();
				b.setBillCode(st.getString("billCode"));
				b.setCreatedBy(st.getLong("createdBy"));
				b.setCreationDate(st.getTimestamp("creationDate"));
				b.setId(st.getLong("id"));
				b.setIsPayment(st.getInt("isPayment"));
				b.setModifyBy(st.getLong("modifyBy"));
				b.setModifyDate(st.getTimestamp("modifyDate"));
				b.setProductCount(st.getDouble("productCount"));
				b.setProductDesc(st.getString("productDesc"));
				b.setProductName(st.getString("productName"));
				b.setProductUnit(st.getString("productUnit"));
				b.setProviderId(st.getInt("providerId"));
				b.setTotalPrice(st.getDouble("totalPrice"));
				list.add(b);
			}
			return list;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			release(ct, ps, st);
		}
		return null;
	}

	@Override
	public Integer[] deleteBatch(String[] id) {
		String sql = " delete from smbms_buy where id in ( ";
		for (int i = 0; i < id.length; i++) {
			if (i < id.length - 1) {
				sql += id[i] + ",";
			} else {
				sql += id[i];
			}
		}
		sql += " );";
		Integer[] num = cud(Buy.class, sql);
		return num;
	}

	@Override
	public int buyRemove(String id) {
		String sql = " delete from smbms_buy where id =?  ";
		Integer[] num = cud(Buy.class, sql, id);
		if (num.length > 0) {
			return num[0];
		}
		return 0;
	}

	@Override
	public Integer[] deleteBuy(String[] id) {
		String sql = " delete from smbms_buy where id in ( ";
		for (int i = 0; i < id.length; i++) {
			if (i < id.length - 1) {
				sql += id[i] + ",";
			} else {
				sql += id[i];
			}
		}
		sql += " );";
		Integer[] num = cud(Buy.class, sql);
		return num;
	}

	// @Test
	// public void y() {
	// System.out.println(getGoodsByid("1"));
	// }

}
